using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTOLayer;

namespace DAOLayer
{
    public class MuonSachDAO:AbstractDao
    {
        public int TestExisted(MuonSachDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            int kq = -1;
            try
            {
                cn.Open();
                String str = "select count(*) from MuonSach where MaSach = "+dto.MaSach+ " and MaDocGia = "+dto.MaDocGia;
                SqlCommand cmd = new SqlCommand(str, cn);
                if ((int)cmd.ExecuteScalar() == 0)
                    kq = 1;
                return kq;
            }
            catch (Exception e)
            {
                return kq;
            }
            finally
            {
                cn.Close();
            }
        }
        public int InsertMuonSach(MuonSachDTO dto)
        {
            int kq = TestExisted(dto);
            if (kq == -1)
            {
                return -1;
            }
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String strSQL = " insert into MuonSach values(";
                strSQL += dto.MaSach + "," + dto.MaDocGia + ",'" + dto.NgayMuonSach + "','" + dto.NgayTraSach + "','" + dto.GhiChu + "')";
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                cmd.ExecuteNonQuery();
                cn.Close();
                return dto.MaMuonSach;
            }
            catch (Exception e)
            {
                return -1;
            }
        }
        public int DeleteMuonSach(int maMS)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Delete from MuonSach where MaMuonSach = "+maMS;
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.ExecuteNonQuery();
                cn.Close();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }
        }
        public int DeleteMuonSachTheoSachDocGia(MuonSachDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Delete from MuonSach where MaDocGia = " + dto.MaDocGia +" and MaSach="+dto.MaSach+" and NgayMuonSach='"+dto.NgayMuonSach+"' and NgayPhaiTra='"+dto.NgayTraSach+"'";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.ExecuteNonQuery();
                cn.Close();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }
        }
        public int UpdateMuonSach(MuonSachDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Update MuonSach set MaSach = ? and MaDocGia = ? and NgayMuonSach = ? and NgayTraSach = ? and GhiChu = ? where MaMuonSach = ?";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters["@MaSach"].Value = dto.MaSach;
                cmd.Parameters.Add("@MaDocGia", SqlDbType.Int);
                cmd.Parameters["@MaDocGia"].Value = dto.MaDocGia;
                cmd.Parameters.Add("@NgayMuonSach", SqlDbType.DateTime);
                cmd.Parameters["@NgayMuonSach"].Value = dto.NgayMuonSach;
                cmd.Parameters.Add("@NgayTraSach", SqlDbType.DateTime);
                cmd.Parameters["@NgayTraSach"].Value = dto.NgayTraSach;
                cmd.Parameters.Add("@GhiChu", SqlDbType.NChar);
                cmd.Parameters["@GhiChu"].Value = dto.GhiChu;
                cmd.Parameters.Add("@MaMuonSach", SqlDbType.Int);
                cmd.Parameters["@MaMuonSach"].Value = dto.MaMuonSach;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }
            finally 
            {
                cn.Close();
            }
        }
        public DataTable LaySachMuonTheoMa(int ma)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select * from MuonSach M ,Sach S,TacGia T,NhaXuatBan N ,TheLoaiSach TL where S.Masach=M.MaSach and S.MaTacGia=T.MatacGia and S.MaNhaXuatBan=N.MaNhaXuatBan and S.maTheLoai=TL.MaTheLoai and S.MaSach=" + ma;
                SqlCommand cmd = new SqlCommand(str, cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                SqlDataAdapter d = new SqlDataAdapter(str, cn);
                d.Fill(dt);
                cn.Close();

            }
            catch (Exception e)
            {
                return null;
            }
            return dt;
        }
        public int TimSachMuon(MuonSachDTO dto)
        {
            DataTable dt = new DataTable();
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            int kq = -1;
            try
            {
                cn.Open();
                String str = "select MaMuonSach from MuonSach where MaSach = " + dto.MaSach + " and MaDocGia = " + dto.MaDocGia+" and NgayMuonSach='"+dto.NgayMuonSach+"' and NgayPhaiTra='"+dto.NgayTraSach+"'";
                SqlCommand cmd = new SqlCommand(str, cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                SqlDataAdapter d = new SqlDataAdapter(str, cn);
                d.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    kq = int.Parse(dt.Rows[0]["MaMuonSach"].ToString());
                }
                cn.Close();
                return kq;
            }
            catch (Exception e)
            {
                return kq;
            }
            
               
            
        }
    }
}
